Information system store enormous amounts of data in databases. These databases can be relational or non-relational containing very structured data or less structured data. Often, databases are transformed to many different formations to ease consumption of the data by various clients. The information in database systems is often stored in tables where a single database can contain many tables.
Many modern applications in retail, finance, science, and so forth, require analysis to be performed over the data stored in databases. Such analysis can include finding the most sales-effective products in a store's inventory or the most financially yielding asset in a portfolio. Business users of all kinds then use business intelligence analytics systems to find answers to business questions that require complex calculations.
To perform these complex calculations, an analytics system uses data models, which are abstractions used to gather information from one or many data sources, such as databases. These abstractions are then queried by the analytics system to perform the necessary analysis so that business users can receive answers to their business questions. The above data models can store data in many forms (cubes, tables, and so forth) depending on the querying application's requirements, format, desired speed of calculation, and so forth.
The data model is created when the analytics system pulls data from external data sources (such as databases) and stores that data wherever the analytics system resides so that the data model could be queried upon request by the analytics system's users. Analytics systems can be based on a client-server architecture, where the data models would be stored on the server side while clients would submit queries. On the other hand, the analytics system can be a single machine deployment where the data model would be stored locally on the computer machine used by the business user that seeks answers for business questions.
Modern spreadsheet programs are able to serve as consumption endpoints for business intelligence and analytics systems' users. They can do so by submitting queries on behalf of the business users that can be run against data models. In MICROSOFT® EXCEL®, there is the ability to both create a data model locally by using the PowerPivot add-in, and to query that data model directly from the instance of EXCEL® by using PivotTables and PivotCharts. The ability to both store and query locally is achieved by using a data model that stores data in the form of collections of tables (hence the name “tabular model” or “tabular data model”) and storing the tabular data model in-memory as part of the EXCEL® workbook in oppose to on-disk as it is done by most database systems and by most analytics systems. The storing in-memory allows rapid access and high bandwidth access to large volumes of data by the EXCEL® client submitting queries against the model.
In order to allow storage of large volumes of data in memory, tabular data models in EXCEL® are compressed using techniques that leverage the relatively low number of unique values across a single column in a table, and storing just the value and its repetition. When the EXCEL® workbook is saved, the model maintains its compression by being stored as minor representation of its footprint in-memory, directly on the machine's disk.